----------------------------------------------
-- Module 1 - Overview
----------------------------------------------

----------------------------------------------
-- Module 2 - Engine/T-SQL
----------------------------------------------

----------------------------------------------
-- Try/Catch

USE AdventureWorks
GO

CREATE PROCEDURE MyProc
AS
BEGIN
 BEGIN TRY 
   SELECT * FROM Person.Address WHERE 1 > 2/0
 END TRY
 BEGIN CATCH
  PRINT 'Error Occurred'
  PRINT ERROR_NUMBER()
  PRINT ERROR_SEVERITY()
  PRINT ERROR_STATE()
  PRINT ERROR_MESSAGE()
 END CATCH
END
GO

EXEC MyProc

----------------------------------------------
-- Common Table Expressions

WITH Median AS
(
SELECT ((MAX(LineTotal) - MIN(LineTotal)) / 2)
  AS MedianValue FROM Sales.SalesOrderDetail
)
SELECT TOP 1000 SalesOrderID, SalesOrderDetailID, LineTotal,
 CASE
  WHEN LineTotal > Median.MedianValue THEN 1
  ELSE 0
 END AS OverHalf
	 FROM Sales.SalesOrderDetail, Median

----------------------------------------------
-- Common Table Expressions (Recursive)

SET NOCOUNT ON

CREATE TABLE MLM (
  ID     int,
  Name   varchar(10),
  Parent int)
GO

INSERT MLM VALUES ( 1,'Bob',null)
INSERT MLM VALUES ( 2,'Amy',1)
INSERT MLM VALUES ( 3,'Jake',1)
INSERT MLM VALUES ( 4,'Kirra',2)
INSERT MLM VALUES ( 5,'Mark',3)
INSERT MLM VALUES ( 6,'Steve',3)
INSERT MLM VALUES ( 7,'Randy',3)
INSERT MLM VALUES ( 8,'Kristen',4)
INSERT MLM VALUES ( 9,'Isaac',6)
INSERT MLM VALUES (10,'Cole',9)
GO

SELECT * FROM MLM
GO

WITH Descendant (Parent,ID,Name) AS
(SELECT Parent, ID, Name
  FROM MLM WHERE ID = 3
UNION ALL
SELECT M.Parent, M.ID, M.Name
 FROM MLM AS M  INNER JOIN
 Descendant AS D ON D.ID = M.Parent)
SELECT ID FROM Descendant
GO

DROP TABLE MLM

----------------------------------------------
-- PIVOT

-- Raw data - expensive (> $10k items) sold in 2002 by quarter

SELECT DatePart(q,OrderDate) as Quarter, P.Name, LineTotal
  FROM Sales.SalesOrderHeader AS H
 INNER JOIN Sales.SalesOrderDetail AS D ON H.SalesOrderID = D.SalesOrderID
 INNER JOIN Production.Product AS P ON D.ProductID = P.ProductID
 WHERE DatePart(yy,OrderDate) = 2002 AND D.LineTotal > 10000

-- Put into a temporary table (optional)

SET NOCOUNT ON

SELECT DatePart(q,OrderDate) as Quarter, P.Name, LineTotal
  INTO #TempSales
  FROM Sales.SalesOrderHeader AS H
 INNER JOIN Sales.SalesOrderDetail AS D ON H.SalesOrderID = D.SalesOrderID
 INNER JOIN Production.Product AS P ON D.ProductID = P.ProductID
 WHERE DatePart(yy,OrderDate) = 2002 AND D.LineTotal > 10000

-- Pivot

SELECT Name, [1] AS 'Q1', [2] AS 'Q2', [3] AS 'Q3', [4] AS 'Q4' 
FROM #TempSales
PIVOT(SUM(LineTotal) 
      FOR Quarter IN ([1], [2], [3], [4])) AS P

-- Done

DROP TABLE #TempSales

----------------------------------------------
-- ROW_NUMBER

-- Return Orders for Customers < 100 with row numbers

SELECT SalesOrderID, CustomerID, 
  ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS Number 
FROM Sales.SalesOrderHeader
WHERE CustomerID < 100

-- Return Orders for Customers < 100 with row numbers and more!

SELECT SalesOrderID, CustomerID, 
  ROW_NUMBER() OVER(ORDER BY CustomerID) AS Number,
  RANK()       OVER(ORDER BY CustomerID) AS [rank],
  DENSE_RANK() OVER(ORDER BY CustomerID) AS [denserank],
  NTILE(5)     OVER(ORDER BY CustomerID) AS ntile5
FROM Sales.SalesOrderHeader
WHERE CustomerID < 100

----------------------------------------------
-- WINDOWING (OVER)

SELECT *,
  RANK() OVER(PARTITION BY Title ORDER BY VacationHours) AS [rank]
from
(
 SELECT E.Title, C.LastName, E.VacationHours 
   FROM HumanResources.Employee AS E
  INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID
) AS A

----------------------------------------------
-- WINDOWING (OVER with MAX aggregate)

SELECT *,
  RANK() OVER(PARTITION BY Title ORDER BY VacationHours) AS [rank],
  MAX(VacationHours) OVER(PARTITION BY Title) as [Most Hours by Title]
from
(
 SELECT E.Title, C.LastName, E.VacationHours 
   FROM HumanResources.Employee AS E
  INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID
) AS A

----------------------------------------------
-- CROSS APPLY and OUTER APPLY

-- Create UDF

CREATE FUNCTION  Greater(@v float, @t float)
 RETURNS TABLE AS
 RETURN SELECT @v AS UDF
 WHERE @v > @t
GO

-- Test

/*
SELECT * FROM dbo.Greater(100,200)
SELECT * FROM dbo.Greater(200,100)
SELECT * FROM dbo.Greater(200,200)

-- Test with CROSS APPLY

SELECT * FROM Sales.SalesOrderDetail AS D
  CROSS APPLY Greater(D.LineTotal, 15000)
 WHERE OrderQty > 10

-- Test with OUTER APPLY

SELECT * FROM Sales.SalesOrderDetail AS D
  OUTER APPLY Greater(D.LineTotal, 15000)
 WHERE OrderQty > 10
*/

-- Done

DROP FUNCTION Greater
GO

----------------------------------------------
-- UPDATE with OUTPUT

SET NOCOUNT OFF

SELECT * FROM Production.Product WHERE Color = 'Black'

--

UPDATE Production.Product SET Color = 'Schwarz' WHERE Color = 'Black'

--

DECLARE @Changes 
 TABLE (ProductID int,
	Name nvarchar(50),
	Color nvarchar(15),
	OldColor nvarchar(15))

UPDATE Production.Product SET Color = 'Black' 
 OUTPUT Inserted.ProductID, Inserted.Name, Inserted.Color, Deleted.Color
 INTO @Changes WHERE Color = 'Schwarz'

SELECT * FROM @Changes

----------------------------------------------
-- Large Objects (LOBs)

CREATE TABLE BigText (ID int, Data text)
INSERT BigText VALUES (1,'Hello World')

--

SELECT * FROM BigText
SELECT UPPER(Data) FROM BigText

--

INSERT BigText VALUES (1,REPLICATE('x',10000))
SELECT * FROM BigText

--

DROP Table BigText
CREATE TABLE BigText (ID int, Data varchar(max))
 
--

INSERT BigText VALUES (1,'Hello World')
INSERT BigText VALUES (1,REPLICATE('x',10000))
SELECT * FROM BigText
SELECT UPPER(Data) FROM BigText

--

DROP Table BigText

----------------------------------------------
-- Synonyms

CREATE SYNONYM Emp
	FOR AdventureWorks.HumanResources.Employee

SELECT * FROM Emp

DROP SYNONYM Emp

----------------------------------------------
-- DDL Triggers

CREATE TRIGGER trgThankYou ON DATABASE FOR CREATE_TABLE
AS PRINT 'Thank you for contributing to AdventureWorks!'

--

CREATE TABLE NewTable (ID int)

--

CREATE TRIGGER trgSafety ON DATABASE FOR DROP_TABLE
AS PRINT 'Sorry, you may not drop tables!' ROLLBACK

--

DROP TABLE NewTable

-- How do you drop DDL triggers? (Have SMS show you ...)

DROP TRIGGER [trgSafety] ON DATABASE
DROP TRIGGER [trgThankYou] ON DATABASE
DROP TABLE NewTable

----------------------------------------------
-- Module 3 - XML
----------------------------------------------

----------------------------------------------
-- XML Data Type

SET NOCOUNT ON
CREATE TABLE Candidate (ID int, Name varchar(50), Resume xml)

-- 

INSERT Candidate VALUES (1,'Smith','<resume>none</resume>')
SELECT * FROM Candidate

-- 

INSERT Candidate VALUES (1,'Smith','none')
INSERT Candidate VALUES (1,'Smith','<resume>none</resume><resume>none</resume>')
SELECT * FROM Candidate

-- 

INSERT Candidate VALUES (1,'Smith','<resume>none</Resume>')
INSERT Candidate VALUES (1,'Smith','no resume & no CV')
 
-- 

TRUNCATE TABLE Candidate
ALTER TABLE Candidate
 ADD CONSTRAINT chkResume CHECK (Resume.exist('//education') = 1)

-- 

INSERT Candidate VALUES (1,'Smith','<resume>none</resume>')

--

INSERT Candidate VALUES (1,'Smith','<resume><education>Harvard</education></resume>')

----------------------------------------------
-- XML Schema

CREATE XML SCHEMA COLLECTION ResumeSchema AS N''

-- Schema

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
		xmlns="resume-schema"
		targetNamespace="resume-schema"
		elementFormDefault="qualified">

<xsd:element name="Resume" type="ResumeType"/>
<xsd:complexType name="ResumeType" mixed="true">
 <xsd:sequence>
  <xsd:element name="Name" type="NameType" minOccurs="0"/>
  <xsd:element name="Objective" type="xsd:string" minOccurs="0"/>
  <xsd:element name="Experience" type="Experience" minOccurs="0" maxOccurs="unbounded"/>
  <xsd:element name="Education" type="Education" minOccurs="0" maxOccurs="unbounded"/>
  <xsd:element name="InterestSection" type="xsd:string" minOccurs="0"/>
 </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Experience" mixed="true">
  <xsd:sequence>
    <xsd:element name="YearFrom" type="xsd:int"/>
    <xsd:element name="YearTo" type="xsd:int"/>
    <xsd:element name="Title" type="xsd:string"/>
    <xsd:element name="Description" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Education" mixed="true">
  <xsd:sequence>
    <xsd:element name="YearFrom" type="xsd:int"/>
    <xsd:element name="YearTo" type="xsd:int"/>
    <xsd:element name="Description" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="NameType" mixed="true">
 <xsd:all>
  <xsd:element name="FirstName" type="xsd:string"/>
  <xsd:element name="LastName" type="xsd:string"/>
 </xsd:all>
</xsd:complexType>
</xsd:schema>

--

SELECT * FROM sys.xml_schema_collections

-- Create Table

DROP TABLE Candidate
CREATE TABLE Candidate (ID int, Name varchar(50), Resume xml (ResumeSchema))

--

INSERT Candidate VALUES (1,'Smith','')

-- Smith.xml

<?xml version="1.0" encoding="utf-8" ?>
<Resume xmlns="resume-schema">
<Name>
<FirstName>Pat</FirstName>
<LastName>Smith</LastName>
</Name>
<Objective>Speak about Yukon and Whidbey</Objective>
<Experience>
<YearFrom>1993</YearFrom>
<YearTo>1999</YearTo>
<Title>Software Developer</Title>
<Description>Built lots of really cool software!</Description>
</Experience>
<Experience>
<YearFrom>2000</YearFrom>
<YearTo>2005</YearTo>
<Title>Trainer and Author</Title>
<Description>Wrote books and spoke at lots of conferences</Description>
</Experience>
<Education>
<YearFrom>1989</YearFrom>
<YearTo>1993</YearTo>
<Description>Podunk University</Description>
</Education>
<InterestSection>I like pudding!</InterestSection>
</Resume>

----------------------------------------------
-- FOR XML Improvements

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML AUTO

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML AUTO, ELEMENTS

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML PATH

--

SELECT Title AS '@Title',
       FirstName as 'Name/First',
       LastName as 'Name/Last',
       EmailAddress as 'Email'
FROM   Person.Contact 
WHERE  ContactID < 10 
FOR XML PATH ('Person')

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS XSINIL

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, XMLDATA

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, XMLSCHEMA

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS, XMLSCHEMA

--

DECLARE @XML XML
SET @XML = 
(SELECT Title, FirstName, LastName, EmailAddress FROM Person.Contact
   WHERE ContactID < 10 FOR XML PATH)
SELECT @XML

----------------------------------------------
-- XQUERY

DECLARE @xmldoc xml
SET @xmldoc = '<people>
 <person>
  <name>
   <givenName>Martin</givenName>
   <familyName>Gudgin</familyName>
  </name>
  <age>33</age>
  <height>short</height>
 </person>
 <person>
  <name>
   <givenName>Simon</givenName>
   <familyName>Horrell</familyName>
  </name>
  <age>40</age>
  <height>short</height>
 </person>
 <person>
  <name>
   <givenName>Mark</givenName>
   <familyName>Szolkowski</familyName>
  </name>
  <age>30</age>
  <height>medium</height>
 </person>
</people>'

SELECT @xmldoc

-- Return names

SELECT @xmldoc.query(' 
(: SQL Server 2005 :)
(: doc function not used :)
for $p in /people/person
return $p/name') 

-- Return people older than 30 (XPATH way)

SELECT @xmldoc.query(' 
(: this uses an XPath predicate :)
/people/person[age > 30]')

-- Return people older than 30 (FLWOR way)

SELECT @xmldoc.query(' 
(: this uses a where  :)
for $p in /people/person
where $p/age > 30
order by $p/age[1]
return $p/name')

-- Getting Creative

SELECT @xmldoc.query(' 
(: this uses a where  :)
for $p in /people/person
where $p/age > 30
order by $p/age[1]
return <foo><bar>{$p/name}</bar></foo>')

----------------------------------------------
-- XML DML

UPDATE HumanResources.JobCandidate SET Resume = ' entire new xml document '

--

DECLARE @myDoc xml       
SET @myDoc = 
 '<Student ID="1" Name="Bill Gates"><Experience></Experience></Student>'
SELECT @myDoc as Example

--

SET @myDoc.modify('insert <Development>Built Microsoft BOB</Development>
 into (/Student/Experience)[1]') 
SELECT @myDoc

--

SET @myDoc.modify('
 replace value of (/Student/Experience/Development[1]/text())[1]
 with "Built Donkey .NET"')
SELECT @myDoc

--

SET @myDoc.modify('delete /Student/Experience/Development[1]')
SELECT @myDoc

----------------------------------------------
-- Module 4 - Security
----------------------------------------------

----------------------------------------------
-- Password policy

USE MASTER
GO

-- Create simple password

CREATE LOGIN ObiWan WITH PASSWORD = 'FOO'
DROP LOGIN ObiWan

-- Run secpol.msc and add strong password policy

CREATE LOGIN ObiWan WITH PASSWORD = 'FOO', CHECK_POLICY = ON
DROP LOGIN ObiWan

----------------------------------------------
-- Certificates

-- Use makecert.exe first (.NET command prompt)
-- c:\makecert.exe sample.cer

USE AdventureWorks
GO

-- Create certificate
 
CREATE CERTIFICATE SampleCert
FROM FILE = 'C:\Sample.cer'
WITH PRIVATE KEY (ENCRYPTION BY PASSWORD  = N'password', DECRYPTION BY PASSWORD  = N'password')
   
-- Verify
 
SELECT * FROM SYS.CERTIFICATES
SELECT Cert_ID('SampleCert')
 
-- Encrypt
 
DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByCert(Cert_ID('SampleCert'), 'SQL Server 2005 Rocks!')
SELECT @Encrypted AS Encrypted
 
-- Decrypt (Not Working!!!)
 
SELECT CONVERT(VARCHAR,DecryptByCert(Cert_ID('SampleCert'),@Encrypted, N'password'))

----------------------------------------------
-- PassPhrase

DECLARE @Encrypted varbinary(256)
SET @Encrypted = EncryptByPassPhrase('P@ssw0rd','SQL Server 2005 Rocks!')

SELECT @Encrypted

SELECT CAST(DecryptByPassPhrase('P@ssw0rd',@Encrypted) AS varchar(256))

----------------------------------------------
-- Module 5 - Reporting Services
----------------------------------------------

---------------------------------------------------------------
-- Module 6 - Managed Code
---------------------------------------------------------------

---------------------------------------------------------------
-- UDF

sing System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class CoolFunctions
{
	[Microsoft.SqlServer.Server.SqlFunction]
	public static SqlString CoolEncrypt(SqlString Data)
	{
		char[] strArray = Data.ToString().ToCharArray();
		Array.Reverse(strArray);
		return new string(strArray);
	}

};

--

SELECT dbo.CoolEncrypt('SQL Server 2005 Rocks!')
SELECT dbo.CoolEncrypt('A man a plan a canal Panama')

---------------------------------------------------------------
-- Update the TEST.SQL and run with F5

---------------------------------------------------------------
-- Add a breakpoint and run with F5

---------------------------------------------------------------
-- Stored Procedure

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Xsl;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void spTransformXML(SqlXml rawXML, SqlXml rawXSLT)
	{
		SqlPipe pipe = SqlContext.Pipe;

		// Cast XML

		XmlDocument xmlDoc = new System.Xml.XmlDocument();
		xmlDoc.LoadXml(rawXML.Value);

		// Cast XSLT

		XmlDocument xslDoc = new System.Xml.XmlDocument();
		xslDoc.LoadXml(rawXSLT.Value);

		// Transform

		XslCompiledTransform xslt = new XslCompiledTransform();
		xslt.Load(xslDoc);
		Stream stream = new MemoryStream();
		XmlWriter wrt = new XmlTextWriter(stream, System.Text.Encoding.UTF8);
		xslt.Transform(xmlDoc, null, wrt);

		// Return HTML

		stream.Position = 0;
		StreamReader SR = new StreamReader(stream);
		pipe.Send(SR.ReadToEnd());
		SR.Close();
	}
};

---------------------------------------------------------------
-- Test

DECLARE @xml xml
SET @xml = '<?xml version="1.0" encoding="UTF-8"?>
<root>
     <person>
          <first>Richard</first>
          <last>Hundhausen</last>
          <title>Trainer</title>
     </person>
     <person>
          <first>Steven</first>
          <last>Borg</last>
          <title>Trainer</title>
     </person>
</root>'

DECLARE @xsl xml
SET @xsl = '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
     <xsl:template match="root">
          <table border="1">
               <tr>
                    <td>First</td>
                    <td>Last</td>
                    <td>Title</td>
               </tr>
               <xsl:apply-templates/>
          </table>
     </xsl:template>
     <xsl:template match="person">
          <tr>
               <xsl:apply-templates/>
          </tr>
     </xsl:template>
     <xsl:template match="first">
          <td>
               <xsl:apply-templates/>
          </td>
     </xsl:template>
     <xsl:template match="last">
          <td>
               <xsl:apply-templates/>
          </td>
     </xsl:template>
     <xsl:template match="title">
          <td>
               <xsl:apply-templates/>
          </td>
     </xsl:template>
</xsl:stylesheet>'

exec spTransformXML @xml, @xsl

---------------------------------------------------------------
-- Debug this sproc using CLR Debugger (NO VS)

----------------------------------------------
-- Module 7 - ADO.NET
----------------------------------------------

---------------------------------------------------------------
-- Asynch - Copy project files

---------------------------------------------------------------
-- Statistics

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			SqlConnection conAW = new SqlConnection("server=(local);database=AdventureWorks;Integrated Security=SSPI;");
			conAW.Open();

			// Enable
			conAW.StatisticsEnabled = true;

			// do some operations
			SqlCommand cmdAW = new SqlCommand("SELECT * FROM Person.Contact", conAW);
			SqlDataReader rdrAW = cmdAW.ExecuteReader();

			// retrieve and process stats
			Hashtable stats = (Hashtable)conAW.RetrieveStatistics();
			IDictionaryEnumerator e = stats.GetEnumerator();
			while (e.MoveNext())
				Console.WriteLine("{0} : {1}", e.Key, e.Value);

			conAW.ResetStatistics();
			conAW.Close();

			Console.ReadLine();
		}
	}
}

---------------------------------------------------------------
-- DataSet and DataTable

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			SqlConnection conAW = new SqlConnection("server=(local);database=AdventureWorks;Integrated Security=SSPI;");
			conAW.Open();

			// DataSet

			DataSet ds = new DataSet();
			SqlDataAdapter adp = new SqlDataAdapter("SELECT TOP 25 * FROM HumanResources.vEmployee",conAW);
			adp.Fill(ds, "Employees");

			// IDataReader

			IDataReader rdrAW = ds.Tables["Employees"].CreateDataReader();
			while (rdrAW.Read())
			{
				Console.WriteLine(rdrAW["LastName"].ToString());
			}

			// Copy Table

			rdrAW = ds.Tables["Employees"].CreateDataReader();
			DataTable tblCopy = new DataTable("Copy");
			tblCopy.Load(rdrAW);

			// Copy Table (The Fast Way)

			tblCopy = new DataTable("Copy");
			tblCopy = ds.Tables["Employees"].Copy();

			// Write XML

			DataTable tblEmp = ds.Tables["Employees"].Copy();
			tblEmp.WriteXml(@"c:\projects\employees.xml");

			conAW.Close();
			Console.ReadLine();
		}
	}
}

---------------------------------------------------------------
-- Update BatchSize

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication1
{
	class Program
	{

		static void RowUpdatedHandler(Object sender, SqlRowUpdatedEventArgs args)
		{
			Console.WriteLine("Just updated " + args.RecordsAffected.ToString() + " rows.");
		}

		static void Main(string[] args)
		{
			SqlConnection conAW = new SqlConnection("server=(local);database=AdventureWorks;Integrated Security=SSPI;");
			conAW.Open();

			// DataSet

			DataSet ds = new DataSet();
			SqlDataAdapter adpAW = new SqlDataAdapter("SELECT EmployeeID, Title, BirthDate FROM HumanResources.Employee",conAW);
			adpAW.Fill(ds, "Employees");

			// Make a bunch of changes

			DataTable tblEmp = ds.Tables["Employees"];
			foreach (DataRow rowEmp in tblEmp.Rows)
				rowEmp["Title"] = DateTime.Now.ToLongTimeString();

			// Create an UpdateCommand

			SqlCommandBuilder bldAW = new SqlCommandBuilder(adpAW);
			adpAW.UpdateCommand = bldAW.GetUpdateCommand();

			// Connect the event

			adpAW.RowUpdated += new SqlRowUpdatedEventHandler(RowUpdatedHandler);

			// Send the changes


			// adpAW.UpdateBatchSize = 50; // Should give us 6 batches (@ 290)
			// adpAW.UpdateBatchSize = 0;

			adpAW.Update(ds, "Employees");

			conAW.Close();
			Console.ReadLine();
		}
	}
}


----------------------------------------------
-- Module 8 - Notifications
----------------------------------------------

----------------------------------------------
-- Query Notifications

INSERT Production.ProductReview(
 ProductID,ReviewerName,ReviewDate,EmailAddress,Rating,Comments)
VALUES ('937','BillG',GetDate(),'billg@microsoft.com',5,
 'Very satisfied. These pedals are fantastic!')

----------------------------------------------
-- Module 9 - SMO
----------------------------------------------

----------------------------------------------
-- EnumerateSMO demo (smo.csv and smo.xls)

----------------------------------------------
-- Various SMO

'Microsoft.SqlServer.ConnectionInfo
'Microsoft.SqlServer.Smo
'Microsoft.SqlServer.SmoEnum

Imports Microsoft.SqlServer.Management.Smo

Public Class Form1

    Private Sub Log(ByVal Msg As String)
        txtOutput.AppendText(Msg & vbCrLf)
    End Sub

    Private Sub ListDatabases(ByVal SRV As Server)
        Log("")
        For Each DB As Database In SRV.Databases
            Log(DB.Name)
        Next
    End Sub

    ' Click event

    Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click

        ' Connect and basics

        Dim SRV As New Server(Environment.MachineName)
        Log(SRV.Name)
        Log(SRV.Information.Edition)
        Log(SRV.Information.VersionString)

        ' List Databases

        Log("")
        Log(SRV.Databases.Count)
        Log("")
        ListDatabases(SRV)

        ' Create a Database

        Try
            Dim newDB As Database
            newDB = New Database(SRV, "Yukon")
            newDB.Create()
        Catch Ex As Exception
            MessageBox.Show(Ex.Message, "Error")
        End Try
        ListDatabases(SRV)

        ' Drop Database

        SRV.Databases("Yukon").Drop()

        ' Request table properties

        Dim TBL As Table
        TBL = SRV.Databases("AdventureWorks").Tables.Item("Contact", "Person")
        Log("")
        Log(TBL.Name) ' lightweight property
        Log(TBL.Schema) ' lightweight property
        Log(TBL.CreateDate) ' must fully instantiate first
        Log(TBL.DataSpaceUsed) ' expensive property

        Dim SCR As New Scripter(SRV)
        SCR.Options.FileName = "c:\projects\script.txt"
        SCR.Options.AppendToFile = True
        SCR.Options.IncludeHeaders = True

        ' URNCollection is a new collection used by SMO

        Dim LST As New UrnCollection
        ' XPath style syntax for reference DB objects
        LST.Add(New Urn("Server[@Name = '" & _
           Environment.MachineName & "']/" & _
           "Database[@Name = 'AdventureWorks']/" & _
           "Table[@Name = 'DatabaseLog']"))

        ' Execute

        SCR.Script(LST)
        Log("Scripting complete")

        Dim CON As Microsoft.SqlServer.Management.Common.ServerConnection = SRV.ConnectionContext
        CON.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
        CON.Connect()

        Log("")
        Log("Executing:")
        Log(CON.ExecuteScalar("SELECT @@Version"))

        Log("")
        Log("Captured:")
        For Each S As String In CON.CapturedSql.Text
            Log(S)
        Next

        CON.Disconnect()

    End Sub

End Class

----------------------------------------------
-- Concordance

-- First, run the MSI to install Northwind (smaller DB)


